﻿Imports System.Data.SqlClient

Public Class frmModuloEmpleados
    Dim Bitacora As New clsActividad
    Dim Usuario As New clsUsuarioPrincipal
    Private minuto As Integer = 0
    Private segundo As Integer = 0
    Private milisegundo As Integer = 0
    Private minutoM As Integer = 0
    Private segundoM As Integer = 0
    Private milisegundoM As Integer = 0
    Private mstrCondicion As String
    Public WriteOnly Property Condicion() As String
        Set(ByVal value As String)
            mstrCondicion = value
        End Set
    End Property
    Private Sub frmModuloEmpleados_FormClosed(sender As Object, e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        Bitacora.RegistraActividad("Cerró al Módulo de Personal")
        Usuario.Ubicacion("Ventana Principal")

        frmMain.statusActividad.Text = ""
    End Sub

    Private Sub frmModuloEmpleados_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        ToolStrip1.Cursor = Cursors.Hand
        ToolStrip1.RenderMode = System.Windows.Forms.ToolStripRenderMode.System

        dgEmpleados.DefaultCellStyle.Font = New Font("Segoe UI", 10, FontStyle.Regular)
        frmMain.statusActividad.Text = ": Módulo de Personal"

        Bitacora.RegistraActividad("Ingresó al Módulo de Personal")
        Usuario.Ubicacion("Módulo de Personal")

        lblStatus.Text = ""

        chActivos.Checked = True

        txtBuscar.Clear()
        CargaEmpleados()
        txtBuscar.Focus()
        dgEmpleados.Columns(5).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
    End Sub
    Sub CargaEmpleados()
        Windows.Forms.Cursor.Current = Cursors.WaitCursor
        dgEmpleados.Rows.Clear()

        Dim Filtro As String
        Filtro = UCase(txtBuscar.Text)
        Filtro = "%" & Filtro.Replace(" ", "%") & "%"

        Dim cnConn As New SqlConnection
        Dim strSql As String
        Dim cmdBuscar As New SqlCommand
        Dim rdBuscar As SqlDataReader


        Try
            cnConn.ConnectionString = CitraConnection

            If chActivos.Checked = False Then
                strSql = "SELECT Empleados.IdEmpleado,Empleados.Codigo,Empleados.NombreCompleto,Departamentos.Descripcion as Departamento,Puestos.Descripcion as Puesto,Empleados.EstadoEmpleado " & _
                   "FROM Empleados " & _
                   "INNER JOIN Departamentos ON Empleados.IdDepartamento=Departamentos.IdDepartamento " & _
                   "INNER JOIN Puestos ON Empleados.IdPuesto=Puestos.IdPuesto " & _
                   "WHERE UPPER(NombreCompleto) Like '" & Filtro & "' " & mstrCondicion & _
                   "OR UPPER(Codigo) Like '" & Filtro & "' " & mstrCondicion & _
                   "OR UPPER(Departamentos.Descripcion) Like '" & Filtro & "' " & mstrCondicion & _
                   "OR UPPER(Puestos.Descripcion) Like '" & Filtro & "' " & mstrCondicion & _
                   "ORDER BY NombreCompleto"
                frmMain.statusActividad.Text = ": Filtro de todos los empleados"
            Else
                strSql = "SELECT Empleados.IdEmpleado,Empleados.Codigo,Empleados.NombreCompleto,Departamentos.Descripcion as Departamento,Puestos.Descripcion as Puesto,Empleados.EstadoEmpleado " & _
                 "FROM Empleados " & _
                 "INNER JOIN Departamentos ON Empleados.IdDepartamento=Departamentos.IdDepartamento " & _
                 "INNER JOIN Puestos ON Empleados.IdPuesto=Puestos.IdPuesto " & _
                 "WHERE EstadoEmpleado<>'B' AND (UPPER(NombreCompleto) Like '" & Filtro & "' " & mstrCondicion & _
                 "OR UPPER(Codigo) Like '" & Filtro & "' " & mstrCondicion & _
                 "OR UPPER(Departamentos.Descripcion) Like '" & Filtro & "' " & mstrCondicion & _
                 "OR UPPER(Puestos.Descripcion) Like '" & Filtro & "' " & mstrCondicion & _
                 ") ORDER BY NombreCompleto"

                frmMain.statusActividad.Text = ": Filtro de empleados activos"
            End If
           

            '"R WHERE A.IdRuta=R.IdRuta AND (UPPER(cCodigoC01) LIKE '" & Filtro & "' " & mstrCondicion & " OR UPPER(NoEconomico) LIKE '" & Filtro & "' " & mstrCondicion & " OR UPPER(Nombre) LIKE '" & Filtro & "' " & mstrCondicion & ")"

            cmdBuscar.Connection = cnConn
            cmdBuscar.CommandText = strSql


            cnConn.Open()
            rdBuscar = cmdBuscar.ExecuteReader

            Dim Status As String = ""
            Dim Cont As Integer = 0

            Do While rdBuscar.Read()
                Cont = Cont + 1
                If rdBuscar("EstadoEmpleado").ToString.Trim = "A" Then
                    Status = "Alta"
                ElseIf rdBuscar("EstadoEmpleado").ToString.Trim = "B" Then
                    Status = "Baja"
                Else
                    Status = "ReIngreso"
                End If
                dgEmpleados.Rows.Add(rdBuscar("IdEmpleado"), rdBuscar("Codigo").ToString.Trim, rdBuscar("NombreCompleto").ToString.Trim, rdBuscar("Departamento").ToString.Trim, rdBuscar("Puesto").ToString.Trim, Status)
                lblStatus.Text = Cont & " Registros"

            Loop
            'dgEmpleados.CurrentCell = dgEmpleados.Rows(0).Cells(1)
        Catch ex As Exception
            MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cnConn.State = ConnectionState.Open Then
                cnConn.Close()
            End If
        End Try
    End Sub

    Private Sub ActualizarCatálogosToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles ActualizarCatálogosToolStripMenuItem.Click
        'Elimina informacion
        Call SetSqlCitra("DELETE FROM Departamentos")
        Call SetSqlCitra("DELETE FROM Puestos")
        Call SetSqlCitra("DELETE FROM Turnos")
        Call SetSqlCitra("DELETE FROM Periodos")
        Departamentos()
        Puestos()
        Turnos()
        Periodos()

        Bitacora.RegistraActividad("actualizó los catalogos del módulo de personal")
        MsgBox("Catálogos actualizados correctamente", MsgBoxStyle.Information, "Aviso")

    End Sub
    Sub Departamentos()
        'Inicializa para departamentos

        Dim cnConn As New SqlClient.SqlConnection
        cnConn.ConnectionString = NominasConnection

        Dim strSql As String = ""
        strSql = "Select IdDepartamento, NumeroDepartamento,Descripcion"
        strSql = strSql & " From NOM10003"
        strSql = strSql & " order by IdDepartamento"

        Dim cmdBuscar As New SqlClient.SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlClient.SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        Dim a As Integer
        Dim b As Integer
        Dim c As String

        Do While rdBuscar.Read()
            a = rdBuscar("IdDepartamento")
            b = rdBuscar("NumeroDepartamento")
            c = rdBuscar("Descripcion").ToString.Trim
            Call SetSqlCitra("Insert Into Departamentos Values (" & a & "," & b & ",'" & c & "')")
        Loop
    End Sub
    Sub Puestos()
        'Inicializa para puestos

        Dim cnConn As New SqlClient.SqlConnection
        cnConn.ConnectionString = NominasConnection

        Dim strSql As String = ""
        strSql = "Select IdPuesto, NumeroPuesto,Descripcion"
        strSql = strSql & " From NOM10006"
        strSql = strSql & " order by IdPuesto"

        Dim cmdBuscar As New SqlClient.SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlClient.SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        Dim a As Integer
        Dim b As Integer
        Dim c As String
        Dim conta As Integer = 0



        Do While rdBuscar.Read()
            a = rdBuscar("IdPuesto")
            b = rdBuscar("NumeroPuesto")
            c = rdBuscar("Descripcion").ToString.Trim
            Call SetSqlCitra("Insert Into Puestos Values (" & a & "," & b & ",'" & c & "')")
        Loop

    End Sub
    Sub Turnos()
        'Inicializa para turnos
        Dim cnConn As New SqlClient.SqlConnection
        cnConn.ConnectionString = NominasConnection

        Dim strSql As String = ""
        strSql = "Select IdTurno, NumeroTurno,Descripcion"
        strSql = strSql & " From NOM10032"
        strSql = strSql & " order by IdTurno"

        Dim cmdBuscar As New SqlClient.SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlClient.SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        Dim a As Integer
        Dim b As Integer
        Dim c As String
        Dim conta As Integer = 0

        Do While rdBuscar.Read()
            a = rdBuscar("IdTurno")
            b = rdBuscar("NumeroTurno")
            c = rdBuscar("Descripcion").ToString.Trim
            Call SetSqlCitra("Insert Into Turnos Values (" & a & "," & b & ",'" & c & "')")
        Loop
    End Sub
    Sub Periodos()
        Dim cnConn As New SqlClient.SqlConnection
        cnConn.ConnectionString = NominasConnection

        Dim strSql As String = ""
        strSql = "Select IdTipoPeriodo,NombreTipoPeriodo"
        strSql = strSql & " From NOM10023"
        strSql = strSql & " order by IdTipoPeriodo"

        Dim cmdBuscar As New SqlClient.SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlClient.SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        Dim a As Integer
        Dim b As String
        Dim conta As Integer = 0

        Do While rdBuscar.Read()
            a = rdBuscar("IdTipoPeriodo")
            b = rdBuscar("NombreTipoPeriodo")
            Call SetSqlCitra("Insert Into Periodos Values (" & a & ",'" & b & "')")
        Loop

    End Sub

    Private Sub ToolStripButton1_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton1.Click
        Close()

    End Sub

    Private Sub RegresarToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles RegresarToolStripMenuItem.Click
        Close()

    End Sub

    Private Sub txtBuscar_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtBuscar.TextChanged
        CargaEmpleados()

    End Sub
    Private Sub dgEmpleados_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgEmpleados.DoubleClick
        Dim valorCell As Integer
        Dim valorRow As DataGridViewRow

        valorCell = Me.dgEmpleados.CurrentCell.ColumnIndex
        valorRow = Me.dgEmpleados.CurrentRow

        EmpleadoID = Me.dgEmpleados.Rows(valorRow.Index).Cells(0).Value.ToString.Trim
        EmpleadoNuevo = False

        valorCellSelect = valorCell
        valorRowSelect = valorRow
        Try
            frmModuloEmpleadosDetalle.ShowDialog()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Aviso")

        End Try

    End Sub

    Private Sub dgEmpleados_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles dgEmpleados.KeyPress
        Dim valorCell As Integer
        Dim valorRow As DataGridViewRow

        valorCell = Me.dgEmpleados.CurrentCell.ColumnIndex
        valorRow = Me.dgEmpleados.CurrentRow

        
        EmpleadoID = Me.dgEmpleados.Rows(valorRow.Index).Cells(0).Value.ToString.Trim
        EmpleadoNuevo = False

        valorCellSelect = valorCell
        valorRowSelect = valorRow

        frmModuloEmpleadosDetalle.ShowDialog()

    End Sub

    Private Sub dgEmpleados_CellContentClick(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgEmpleados.CellContentClick

    End Sub

    Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs)


    End Sub

    Private Sub ToolStripButton3_Click(sender As System.Object, e As System.EventArgs)

    End Sub

    Private Sub RegistrarToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles RegistrarToolStripMenuItem.Click
        EmpleadoNuevo = True
        frmModuloEmpleadosDetalle.ShowDialog()
    End Sub
    Sub buscaNuevo()
        buscarcargos()
        Application.DoEvents()
        buscaPagos()
        Application.DoEvents()
        buscabajas()
        Application.DoEvents()
        buscaIncapacidades()
        Application.DoEvents()
        buscaVacaciones()
        Application.DoEvents()
        buscaInfonavit()
        Application.DoEvents()
        buscarPermisos()
        Application.DoEvents()
        buscarOtros()
        Application.DoEvents()
        BuscaFianzas()
        Application.DoEvents()
        BuscaObservaciones()
        Application.DoEvents()
        buscaFonacot()
        Application.DoEvents()
        BuscaAltasNuevas()
        Application.DoEvents()
    End Sub

    Private Sub GenerarAvisoToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles GenerarAvisoToolStripMenuItem.Click
        buscaNuevo()
    End Sub
    Sub BuscaAltasNuevas()
        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = "SELECT * From Altas " & _
        " WHERE reportado='No'"

        Dim rdBuscar As SqlDataReader

        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader
        Dim resp As Integer
        Dim cont As Integer = 0

        Do While rdBuscar.Read()
            cont = cont + 1
        Loop
        Try
            If cont > 0 Then
                resp = MsgBox("Tienes " & cont & " Altas(s) nueva(s), ¿Deseas verla(s) ahora?", MsgBoxStyle.YesNo, "Aviso")
                If resp = 6 Then
                    frmAltasNuevas.ShowDialog()
                End If
            End If
        Catch ex As Exception

        End Try
    End Sub
    Sub BuscaFianzas()
        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = "SELECT * From Fianzas " & _
        " WHERE reportado='No' and fianza>0 order by fecha"

        Dim rdBuscar As SqlDataReader

        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader
        Dim resp As String
        Dim cont As Integer = 0

        Do While rdBuscar.Read()
            cont = cont + 1
        Loop
        If cont > 0 Then
            resp = MsgBox("Tienes " & cont & " fianza(s) nueva(s), ¿Deseas verla(s) ahora?", MsgBoxStyle.YesNo, "Aviso")
            If resp = 6 Then
                frmFianzasNuevas.ShowDialog()
            End If
        End If
    End Sub
    Sub BuscaObservaciones()
        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = "SELECT * From Observaciones " & _
        " WHERE reportado='No' order by fecha"

        Dim rdBuscar As SqlDataReader

        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader
        Dim resp As String
        Dim cont As Integer = 0

        Do While rdBuscar.Read()
            cont = cont + 1
        Loop

        If cont > 0 Then
            resp = MsgBox("Tienes " & cont & " observaciones(s) nueva(s), ¿Deseas verla(s) ahora?", MsgBoxStyle.YesNo, "Aviso")
            If resp = 6 Then
                frmObsNuevas.ShowDialog()
            End If
        End If
    End Sub
    Sub buscarOtros()
        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = "SELECT * From OtrosCargos " & _
        " WHERE reportado='No' order by fecha"

        Dim rdBuscar As SqlDataReader

        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader
        Dim resp As String
        Dim cont As Integer = 0

        Do While rdBuscar.Read()
            cont = cont + 1
        Loop

        If cont > 0 Then
            resp = MsgBox("Tienes " & cont & " cargo(s) diversos nuevo(s), ¿Deseas verlo(s) ahora?", MsgBoxStyle.YesNo, "Aviso")
            If resp = 6 Then
                frmOtrosNuevos.ShowDialog()
            End If
        End If
    End Sub
    Sub buscarcargos()
        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = "SELECT * From Accidentes " & _
        " WHERE reportado='No' order by fecha"

        Dim rdBuscar As SqlDataReader

        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader
        Dim resp As String
        Dim cont As Integer = 0

        Do While rdBuscar.Read()
            cont = cont + 1
        Loop

        If cont > 0 Then
            resp = MsgBox("Tienes " & cont & " cargo(s) nuevo(s), ¿Deseas verlo(s) ahora?", MsgBoxStyle.YesNo, "Aviso")
            If resp = 6 Then
                frmCargosNuevos.ShowDialog()
            End If
        End If

    End Sub
    Sub buscarPermisos()
        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = "SELECT * From Permisos " & _
        " WHERE reportado='No' order by dia"

        Dim rdBuscar As SqlDataReader

        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader
        Dim resp As String
        Dim cont As Integer = 0

        Do While rdBuscar.Read()
            cont = cont + 1
        Loop

        If cont > 0 Then
            resp = MsgBox("Tienes " & cont & " permiso(s) nuevo(s), ¿Deseas verlo(s) ahora?", MsgBoxStyle.YesNo, "Aviso")
            If resp = 6 Then
                frmPermisosNuevos.ShowDialog()
            End If
        End If

    End Sub
    Sub buscaPagos()
        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = "SELECT * From Gratificaciones " & _
        " WHERE notificado='No' order by fecha"

        Dim rdBuscar As SqlDataReader

        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader
        Dim resp As String
        Dim cont As Integer = 0

        Do While rdBuscar.Read()
            cont = cont + 1
        Loop
        Try
            If cont > 0 Then
                resp = MsgBox("Tienes " & cont & " pago(s) nuevo(s), ¿Deseas verlo(s) ahora?", MsgBoxStyle.YesNo, "Aviso")
                If resp = 6 Then
                    frmGratiNuevo.ShowDialog()
                End If
            End If
        Catch ex As Exception

        End Try
    End Sub
    Sub buscaVacaciones()
        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = "SELECT * From Vacaciones " & _
        " WHERE reportado='No' order by idVacacion"

        Dim rdBuscar As SqlDataReader

        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader
        Dim resp As String
        Dim cont As Integer = 0




        Do While rdBuscar.Read()
            cont = cont + 1
        Loop

        Try
            If cont > 0 Then
                resp = MsgBox("Tienes " & cont & " vacacion(es) nueva(s), ¿Deseas verla(s) ahora?", MsgBoxStyle.YesNo, "Aviso")
                If resp = 6 Then
                    frmVacaNuevas.ShowDialog()
                End If
            End If
        Catch ex As Exception

        End Try
    End Sub
    Sub buscabajas()
        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = "SELECT * From Bajas " & _
        " WHERE reportado='No'"

        Dim rdBuscar As SqlDataReader

        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader
        Dim resp As Integer
        Dim cont As Integer = 0

        Do While rdBuscar.Read()
            cont = cont + 1
        Loop
        Try
            If cont > 0 Then
                resp = MsgBox("Tienes " & cont & " baja(s) nueva(s), ¿Deseas verla(s) ahora?", MsgBoxStyle.YesNo, "Aviso")
                If resp = 6 Then
                    frmBajasNuevas.ShowDialog()
                End If
            End If
        Catch ex As Exception

        End Try
    End Sub
    Sub buscaIncapacidades()
        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = "SELECT * From Incapacidades " & _
        " WHERE reportado='No'"

        Dim rdBuscar As SqlDataReader

        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader
        Dim resp As Integer
        Dim cont As Integer = 0

        Do While rdBuscar.Read()
            cont = cont + 1
        Loop

        Try
            If cont > 0 Then
                resp = MsgBox("Tienes " & cont & " incapacidad(es) nueva(s), ¿Deseas verla(s) ahora?", MsgBoxStyle.YesNo, "Aviso")
                If resp = 6 Then
                    frmIncaNuevos1.ShowDialog()
                End If
            End If
        Catch ex As Exception

        End Try
    End Sub
    Sub buscaInfonavit()
        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = "SELECT * From InfonavitFonacot " & _
        " WHERE reportado='No' and tipo='INFONAVIT'"

        Dim rdBuscar As SqlDataReader

        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader
        Dim resp As Integer
        Dim cont As Integer = 0

        Do While rdBuscar.Read()
            cont = cont + 1
        Loop

        Try
            If cont > 0 Then
                resp = MsgBox("Tienes " & cont & " registro(s) nuevo(s) de Infonavit, ¿Deseas verlo(s) ahora?", MsgBoxStyle.YesNo, "Aviso")
                If resp = 6 Then
                    frmInfoNuevos.ShowDialog()
                End If
            End If
        Catch ex As Exception

        End Try
    End Sub
    Sub buscaFonacot()
        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = "SELECT * From InfonavitFonacot " & _
        " WHERE reportado='No' and tipo='FONACOT'"

        Dim rdBuscar As SqlDataReader

        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader
        Dim resp As Integer
        Dim cont As Integer = 0

        Do While rdBuscar.Read()
            cont = cont + 1
        Loop

        Try
            If cont > 0 Then
                resp = MsgBox("Tienes " & cont & " registro(s) nuevo(s) de Fonacot, ¿Deseas verlo(s) ahora?", MsgBoxStyle.YesNo, "Aviso")
                If resp = 6 Then
                    frmFonacotNuevos.ShowDialog()
                End If
            End If
        Catch ex As Exception

        End Try
    End Sub

    Private Sub GratificacionesToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles GratificacionesToolStripMenuItem.Click
        frmRGratificacion.ShowDialog()
    End Sub

    Private Sub AccidentesToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles AccidentesToolStripMenuItem.Click
        frmRAccidentes.ShowDialog()
    End Sub

    Private Sub IncidentesToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles IncidentesToolStripMenuItem.Click
        frmRIncidencias.ShowDialog()
    End Sub

    Private Sub ReportesToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles ReportesToolStripMenuItem.Click
        frmRReportes.ShowDialog()
    End Sub

    Private Sub IncapacidadesToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles IncapacidadesToolStripMenuItem.Click
        frmRIncapacidades.ShowDialog()
    End Sub

    Private Sub UniformesToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles UniformesToolStripMenuItem.Click
        frmRUniformes.ShowDialog()

    End Sub

    Private Sub CapacitacionesToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles CapacitacionesToolStripMenuItem.Click
        frmRCapacitaciones.ShowDialog()
    End Sub

    Private Sub EvaluacionesToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles EvaluacionesToolStripMenuItem.Click
        frmREvaluaciones.ShowDialog()
    End Sub

    Private Sub CumpleañosToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles CumpleañosToolStripMenuItem.Click
        frmRCumpleaños.ShowDialog()
    End Sub

    Private Sub OperadoresTransporteToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles OperadoresTransporteToolStripMenuItem.Click
        frmREmpleadosTrans.ShowDialog()
    End Sub

    Private Sub LicenciasToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles LicenciasToolStripMenuItem.Click
        frmRLicencias.ShowDialog()
    End Sub

    Private Sub TarjetonesToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles TarjetonesToolStripMenuItem.Click
        frmRTarjetones.ShowDialog()
    End Sub

    Private Sub VacacionesToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles VacacionesToolStripMenuItem.Click
        frmRVacaciones.ShowDialog()
    End Sub

    Private Sub PermisosToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles PermisosToolStripMenuItem.Click
        frmRPermisos.ShowDialog()

    End Sub

    Private Sub InfonavitFonacotToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles InfonavitFonacotToolStripMenuItem.Click
        frmRInfonavit.ShowDialog()
    End Sub

    Private Sub CargosVariosToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles CargosVariosToolStripMenuItem.Click
        frmROtrosCargos.ShowDialog()
    End Sub

    Private Sub SaldosInfonavitToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles SaldosInfonavitToolStripMenuItem.Click
        frmRSaldosInfonavit.ShowDialog()
    End Sub

    Private Sub OperadoresToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Dim xl As Object
        Dim wb As Object
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 6

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim strSql As String = ""

        strSql = "select IDEmpleado,D.Descripcion as depto,P.Descripcion as puesto,n.Nombre as Nombrecillo,T.Descripcion as turno,Codigo,e.Nombre,ApellidoPaterno,FechaReingreso,ApellidoMaterno,Nombrecompleto,FechaNacimiento,LugarNacimiento,EstadoCivil,Sexo,CURP,CURPF,NSS,RFC,Homoclave,CuentaPago,EstadoEmpleado,SD,SDI,FechaAlta,CuentaCW,Telefono,CodigoPostal,Direccion,Poblacion,Estado,FechaBaja,CausaBaja,celular,telefonorecados,escolaridad"
        strSql = strSql & " from Empleados E INNER JOIN Departamentos D"
        strSql = strSql & " ON E.IdDepartamento=D.IdDepartamento"
        strSql = strSql & " INNER JOIN Puestos P ON E.IdPuesto=P.IdPuesto"
        strSql = strSql & " INNER JOIN Periodos N ON E.IdPeriodo=N.IdPeriodo"
        strSql = strSql & " INNER JOIN Turnos T ON E.IdTurno=T.IdTurno"
        strSql = strSql & " WHERE p.Descripcion='OPERADOR'"
        strSql = strSql & " and EstadoEmpleado='A'"
        strSql = strSql & " order by IdEmpleado"


        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        wb.SHEETS(1).cells(rng, 1).value = "Codigo"
        wb.SHEETS(1).cells(rng, 2).value = "Nombre"
        wb.SHEETS(1).cells(rng, 3).value = "Apellido Paterno"
        wb.SHEETS(1).cells(rng, 4).value = "Apellido Materno"
        wb.SHEETS(1).cells(rng, 5).value = "Nombre Completo"
        wb.SHEETS(1).cells(rng, 6).value = "Fecha Nacimiento"
        wb.SHEETS(1).cells(rng, 7).value = "Lugar Nacimiento"
        wb.SHEETS(1).cells(rng, 8).value = "Estado Civil"
        wb.SHEETS(1).cells(rng, 9).value = "Sexo"
        wb.SHEETS(1).cells(rng, 10).value = "CURP"
        wb.SHEETS(1).cells(rng, 11).value = "No. IMSS"
        wb.SHEETS(1).cells(rng, 12).value = "RFC"
        wb.SHEETS(1).cells(rng, 13).value = "Cuenta Banco"
        wb.SHEETS(1).cells(rng, 14).value = "SD"
        wb.SHEETS(1).cells(rng, 15).value = "SDI"
        wb.SHEETS(1).cells(rng, 16).value = "Departamento"
        wb.SHEETS(1).cells(rng, 17).value = "Puesto"
        wb.SHEETS(1).cells(rng, 18).value = "Periodo"
        wb.SHEETS(1).cells(rng, 19).value = "Turno"
        wb.SHEETS(1).cells(rng, 20).value = "Cuenta Compaq"
        wb.SHEETS(1).cells(rng, 21).value = "Direccion"
        wb.SHEETS(1).cells(rng, 22).value = "Codigo Postal"
        wb.SHEETS(1).cells(rng, 23).value = "Ciudad"
        wb.SHEETS(1).cells(rng, 24).value = "Estado"
        wb.SHEETS(1).cells(rng, 25).value = "Telefono"
        wb.SHEETS(1).cells(rng, 26).value = "Tel Celular"
        wb.SHEETS(1).cells(rng, 27).value = "Tel Recados"
        wb.SHEETS(1).cells(rng, 28).value = "Escolaridad"
        wb.SHEETS(1).cells(rng, 29).value = "Estado Empleado"
        wb.SHEETS(1).cells(rng, 30).value = "Fecha Alta"
        wb.SHEETS(1).cells(rng, 31).value = "Fecha Reingreso"
        wb.SHEETS(1).cells(rng, 32).value = "Fecha Baja"
        wb.SHEETS(1).cells(rng, 33).value = "Causa de la Baja"
        wb.SHEETS(1).cells(rng, 34).value = "Tarjeton"
        wb.SHEETS(1).cells(rng, 35).value = "Licencia"
        wb.SHEETS(1).cells(rng, 36).value = "Vigencia"

        Dim i As Integer
        For i = 1 To 36
            wb.SHEETS(1).cells(rng, i).font.bold = True
            wb.SHEETS(1).cells(rng, i).font.size = 9
        Next

        Dim fNac As Date
        Dim dia As String = ""
        Dim mes As String = ""
        Dim strFnac As String = ""
        Dim sexo As String = ""
        Dim estado As String = ""
        Dim ALTA As Date
        Dim re As Date
        Dim baja As Date
        Dim Civil As String = ""
        Dim anio As String = ""

        rng = rng + 1
        Dim cont As Integer = 0

        Do While rdBuscar.Read()
            cont = cont + 1
            dia = ""
            mes = ""
            strFnac = ""
            sexo = ""
            estado = ""
            Civil = ""
            anio = ""
            wb.SHEETS(1).cells(rng, 1).value = rdBuscar("Codigo").ToString.Trim
            wb.SHEETS(1).cells(rng, 2).value = rdBuscar("Nombre").ToString.Trim
            wb.SHEETS(1).cells(rng, 3).value = rdBuscar("ApellidoPaterno").ToString.Trim
            wb.SHEETS(1).cells(rng, 4).value = rdBuscar("ApellidoMaterno").ToString.Trim
            wb.SHEETS(1).cells(rng, 5).value = rdBuscar("Nombrecompleto").ToString.Trim
            fNac = rdBuscar("FechaNacimiento")
            dia = fNac.Day
            mes = fNac.Month
            If dia < 10 Then
                dia = "0" & dia
            End If
            If mes < 10 Then
                mes = "0" & mes
            End If
            anio = Mid(fNac.Year, 3, 2)
            strFnac = anio & mes & dia
            wb.SHEETS(1).cells(rng, 6).value = fNac.Day & "/" & fNac.Month & "/" & fNac.Year
            wb.SHEETS(1).cells(rng, 7).value = rdBuscar("LugarNacimiento").ToString.Trim
            If rdBuscar("EstadoCivil").ToString.Trim = "C" Then
                Civil = "Casado"
            ElseIf rdBuscar("EstadoCivil").ToString.Trim = "S" Then
                Civil = "Soltero"
            ElseIf rdBuscar("EstadoCivil").ToString.Trim = "D" Then
                Civil = "Divorciado"
            ElseIf rdBuscar("EstadoCivil").ToString.Trim = "V" Then
                Civil = "Viudo"

            End If

            wb.SHEETS(1).cells(rng, 8).value = Civil
            If rdBuscar("Sexo").ToString.Trim = "M" Then
                sexo = "Masculino"
            Else
                sexo = "Femenino"
            End If
            wb.SHEETS(1).cells(rng, 9).value = sexo
            wb.SHEETS(1).cells(rng, 10).value = rdBuscar("CURP").ToString.Trim & strFnac & rdBuscar("CURPF").ToString.Trim
            wb.SHEETS(1).cells(rng, 11).value = rdBuscar("Nss").ToString.Trim
            wb.SHEETS(1).cells(rng, 12).value = rdBuscar("RFC").ToString.Trim & strFnac & rdBuscar("Homoclave").ToString.Trim
            wb.SHEETS(1).cells(rng, 13).value = rdBuscar("CuentaPago")
            wb.SHEETS(1).cells(rng, 14).value = rdBuscar("Sd")
            wb.SHEETS(1).cells(rng, 15).value = rdBuscar("Sdi")
            wb.SHEETS(1).cells(rng, 16).value = rdBuscar("depto").ToString.Trim
            wb.SHEETS(1).cells(rng, 17).value = rdBuscar("puesto").ToString.Trim
            wb.SHEETS(1).cells(rng, 18).value = rdBuscar("Nombrecillo").ToString.Trim
            wb.SHEETS(1).cells(rng, 19).value = rdBuscar("Turno").ToString.Trim
            wb.SHEETS(1).cells(rng, 20).value = rdBuscar("CuentaCW").ToString.Trim
            wb.SHEETS(1).cells(rng, 21).value = rdBuscar("Direccion").ToString.Trim
            wb.SHEETS(1).cells(rng, 22).value = rdBuscar("CodigoPostal")
            wb.SHEETS(1).cells(rng, 23).value = rdBuscar("Poblacion").ToString.Trim
            wb.SHEETS(1).cells(rng, 24).value = rdBuscar("Estado").ToString.Trim
            wb.SHEETS(1).cells(rng, 25).value = rdBuscar("Telefono").ToString.Trim
            wb.SHEETS(1).cells(rng, 26).value = rdBuscar("celular").ToString.Trim
            wb.SHEETS(1).cells(rng, 27).value = rdBuscar("telefonorecados").ToString.Trim
            wb.SHEETS(1).cells(rng, 28).value = rdBuscar("escolaridad").ToString.Trim
            If rdBuscar("EstadoEmpleado").ToString.Trim = "A" Then
                estado = "Alta"
            ElseIf rdBuscar("EstadoEmpleado").ToString.Trim = "B" Then
                estado = "Baja"
            Else
                estado = "Reingreso"
            End If
            wb.SHEETS(1).cells(rng, 29).value = estado
            ALTA = rdBuscar("FechaAlta")
            wb.SHEETS(1).cells(rng, 30).value = ALTA.Day & "/" & ALTA.Month & "/" & ALTA.Year
            Try
                re = rdBuscar("FechaReingreso")
                baja = rdBuscar("FechaBaja")
            Catch ex As Exception

            End Try
            If estado <> "Alta" Then
                wb.SHEETS(1).cells(rng, 31).value = re.Day & "/" & re.Month & "/" & re.Year
                wb.SHEETS(1).cells(rng, 32).value = baja.Day & "/" & baja.Month & "/" & baja.Year

            End If
            Dim vigencia As Date

            wb.SHEETS(1).cells(rng, 33).value = rdBuscar("CausaBaja").ToString.Trim

            If rdBuscar("puesto").ToString.Trim = "OPERADOR" Then
                wb.SHEETS(1).cells(rng, 34).value = BuscarStrDatoCitra("Capacitacion", "Tarjeton", "status='ALTA' and idempleado=" & rdBuscar("idempleado"))
                wb.SHEETS(1).cells(rng, 35).value = BuscarStrDatoCitra("Licencias", "Licencia", "idempleado=" & rdBuscar("idempleado"))
                vigencia = BuscarDateDatoCitra("Licencias", "vigencia", "idempleado=" & rdBuscar("idempleado"))
                wb.SHEETS(1).cells(rng, 36).value = vigencia.Day & "/" & vigencia.Month & "/" & vigencia.Year
            Else
                wb.SHEETS(1).cells(rng, 34).value = ""
                wb.SHEETS(1).cells(rng, 35).value = ""
                wb.SHEETS(1).cells(rng, 36).value = ""
            End If
            For i = 1 To 36
                wb.SHEETS(1).cells(rng, i).font.size = 9
            Next
            rng = rng + 1
        Loop

        wb.SHEETS(1).columns(14).numberformat = "#,##0.00"
        wb.SHEETS(1).columns(15).numberformat = "#,##0.00"

        wb.SHEETS(1).COLUMNS(1).ColumnWidth = 8
        wb.SHEETS(1).COLUMNS(2).ColumnWidth = 22
        wb.SHEETS(1).COLUMNS(3).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(4).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(5).ColumnWidth = 40
        wb.SHEETS(1).COLUMNS(6).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(7).ColumnWidth = 20
        wb.SHEETS(1).COLUMNS(8).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(9).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(10).ColumnWidth = 25
        wb.SHEETS(1).COLUMNS(11).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(12).ColumnWidth = 25
        wb.SHEETS(1).COLUMNS(13).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(14).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(15).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(16).ColumnWidth = 32
        wb.SHEETS(1).COLUMNS(17).ColumnWidth = 30
        wb.SHEETS(1).COLUMNS(18).ColumnWidth = 18
        wb.SHEETS(1).COLUMNS(19).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(20).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(21).ColumnWidth = 48
        wb.SHEETS(1).COLUMNS(22).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(23).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(24).ColumnWidth = 20
        wb.SHEETS(1).COLUMNS(25).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(26).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(27).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(28).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(29).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(30).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(31).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(32).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(33).ColumnWidth = 40
        wb.SHEETS(1).COLUMNS(34).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(35).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(36).ColumnWidth = 12



        wb.SHEETS(1).cells(1, 1).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 1).value = "Informe de Personal detallado. Filtro: Operadores Activos"
        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Impreso por: " & UsuarioActivo & " el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 8
        wb.SHEETS(1).cells(4, 1).value = "Registros del filtro: " & cont
        wb.SHEETS(1).cells(4, 1).FONT.SIZE = 8

    End Sub

    Private Sub EnExcelToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Dim xl As Object
        Dim wb As Object
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 6

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim strSql As String = ""
        Dim Filtro As String
        Filtro = UCase(txtBuscar.Text)
        Filtro = "%" & Filtro.Replace(" ", "%") & "%"

        If chActivos.Checked = False Then
            strSql = "select IDEmpleado,D.Descripcion as depto,P.Descripcion as puesto,n.Nombre as Nombrecillo,T.Descripcion as turno,Codigo,e.Nombre,ApellidoPaterno,FechaReingreso,ApellidoMaterno,Nombrecompleto,FechaNacimiento,LugarNacimiento,EstadoCivil,Sexo,CURP,CURPF,NSS,RFC,Homoclave,CuentaPago,EstadoEmpleado,SD,SDI,FechaAlta,CuentaCW,Telefono,CodigoPostal,Direccion,Poblacion,Estado,FechaBaja,CausaBaja,celular,telefonorecados,escolaridad" & _
               " FROM Empleados E " & _
               "INNER JOIN D ON E.IdDepartamento=D.IdDepartamento " & _
               "INNER JOIN Puestos P ON E.IdPuesto=P.IdPuesto " & _
               "WHERE UPPER(NombreCompleto) Like '" & Filtro & "' " & mstrCondicion & _
               "OR UPPER(Codigo) Like '" & Filtro & "' " & mstrCondicion & _
               "OR UPPER(D.Descripcion) Like '" & Filtro & "' " & mstrCondicion & _
               "OR UPPER(P.Descripcion) Like '" & Filtro & "' " & mstrCondicion & _
               "ORDER BY NombreCompleto"
        Else
            strSql = "select IDEmpleado,D.Descripcion as depto,P.Descripcion as puesto,Codigo,e.Nombre,ApellidoPaterno,FechaReingreso,ApellidoMaterno,Nombrecompleto,FechaNacimiento,LugarNacimiento,EstadoCivil,Sexo,CURP,CURPF,NSS,RFC,Homoclave,CuentaPago,EstadoEmpleado,SD,SDI,FechaAlta,CuentaCW,Telefono,CodigoPostal,Direccion,Poblacion,Estado,FechaBaja,CausaBaja,celular,telefonorecados,escolaridad" & _
             " FROM Empleados E " & _
             "INNER JOIN Departamentos D ON E.IdDepartamento=D.IdDepartamento " & _
             "INNER JOIN Puestos P ON E.IdPuesto=P.IdPuesto " & _
             "WHERE EstadoEmpleado<>'B' AND (UPPER(NombreCompleto) Like '" & Filtro & "' " & mstrCondicion & _
             "OR UPPER(Codigo) Like '" & Filtro & "' " & mstrCondicion & _
             "OR UPPER(D.Descripcion) Like '" & Filtro & "' " & mstrCondicion & _
             "OR UPPER(P.Descripcion) Like '" & Filtro & "' " & mstrCondicion & _
             ") ORDER BY NombreCompleto"
        End If

        'strSql = "select IDEmpleado,D.Descripcion as depto,P.Descripcion as puesto,n.Nombre as Nombrecillo,T.Descripcion as turno,Codigo,e.Nombre,ApellidoPaterno,FechaReingreso,ApellidoMaterno,Nombrecompleto,FechaNacimiento,LugarNacimiento,EstadoCivil,Sexo,CURP,CURPF,NSS,RFC,Homoclave,CuentaPago,EstadoEmpleado,SD,SDI,FechaAlta,CuentaCW,Telefono,CodigoPostal,Direccion,Poblacion,Estado,FechaBaja,CausaBaja,celular,telefonorecados,escolaridad"
        'strSql = strSql & " from Empleados E INNER JOIN Departamentos D"
        'strSql = strSql & " ON E.IdDepartamento=D.IdDepartamento"
        'strSql = strSql & " INNER JOIN Puestos P ON E.IdPuesto=P.IdPuesto"
        'strSql = strSql & " INNER JOIN Periodos N ON E.IdPeriodo=N.IdPeriodo"
        'strSql = strSql & " INNER JOIN Turnos T ON E.IdTurno=T.IdTurno"
        'strSql = strSql & " WHERE p.Descripcion='OPERADOR'"
        'strSql = strSql & " and EstadoEmpleado='A'"
        'strSql = strSql & " order by IdEmpleado"


        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        wb.SHEETS(1).cells(rng, 2).value = "Codigo"
        wb.SHEETS(1).cells(rng, 3).value = "Nombre"
        wb.SHEETS(1).cells(rng, 4).value = "Apellido Paterno"
        wb.SHEETS(1).cells(rng, 5).value = "Apellido Materno"
        wb.SHEETS(1).cells(rng, 6).value = "Nombre Completo"
        wb.SHEETS(1).cells(rng, 7).value = "Fecha Nacimiento"
        wb.SHEETS(1).cells(rng, 8).value = "Lugar Nacimiento"
        wb.SHEETS(1).cells(rng, 9).value = "Estado Civil"
        wb.SHEETS(1).cells(rng, 10).value = "Sexo"
        wb.SHEETS(1).cells(rng, 11).value = "CURP"
        wb.SHEETS(1).cells(rng, 12).value = "No. IMSS"
        wb.SHEETS(1).cells(rng, 13).value = "RFC"
        wb.SHEETS(1).cells(rng, 14).value = "Cuenta Banco"
        wb.SHEETS(1).cells(rng, 15).value = "SD"
        wb.SHEETS(1).cells(rng, 16).value = "SDI"
        wb.SHEETS(1).cells(rng, 17).value = "Departamento"
        wb.SHEETS(1).cells(rng, 18).value = "Puesto"
        wb.SHEETS(1).cells(rng, 19).value = "Cuenta Compaq"
        wb.SHEETS(1).cells(rng, 20).value = "Direccion"
        wb.SHEETS(1).cells(rng, 21).value = "Codigo Postal"
        wb.SHEETS(1).cells(rng, 22).value = "Ciudad"
        wb.SHEETS(1).cells(rng, 23).value = "Estado"
        wb.SHEETS(1).cells(rng, 24).value = "Telefono"
        wb.SHEETS(1).cells(rng, 25).value = "Tel Celular"
        wb.SHEETS(1).cells(rng, 26).value = "Tel Recados"
        wb.SHEETS(1).cells(rng, 27).value = "Escolaridad"
        wb.SHEETS(1).cells(rng, 28).value = "Estado Empleado"
        wb.SHEETS(1).cells(rng, 29).value = "Fecha Alta"
        wb.SHEETS(1).cells(rng, 30).value = "Fecha Reingreso"
        wb.SHEETS(1).cells(rng, 31).value = "Fecha Baja"
        wb.SHEETS(1).cells(rng, 32).value = "Causa de la Baja"
        wb.SHEETS(1).cells(rng, 33).value = "Tarjeton"
        wb.SHEETS(1).cells(rng, 34).value = "Licencia"
        wb.SHEETS(1).cells(rng, 35).value = "Vigencia"

        Dim i As Integer
        For i = 2 To 35
            wb.SHEETS(1).cells(rng, i).font.bold = True
            wb.SHEETS(1).cells(rng, i).font.size = 9
        Next

        Dim fNac As Date
        Dim dia As String = ""
        Dim mes As String = ""
        Dim strFnac As String = ""
        Dim sexo As String = ""
        Dim estado As String = ""
        Dim ALTA As Date
        Dim re As Date
        Dim baja As Date
        Dim Civil As String = ""
        Dim anio As String = ""
        Dim vigencia As Date


        rng = rng + 1
        Dim cont As Integer = 0
        Do While rdBuscar.Read()
            cont = cont + 1
            dia = ""
            mes = ""
            strFnac = ""
            sexo = ""
            estado = ""
            Civil = ""
            anio = ""
            wb.SHEETS(1).cells(rng, 2).value = rdBuscar("Codigo").ToString.Trim
            wb.SHEETS(1).cells(rng, 3).value = rdBuscar("Nombre").ToString.Trim
            wb.SHEETS(1).cells(rng, 4).value = rdBuscar("ApellidoPaterno").ToString.Trim
            wb.SHEETS(1).cells(rng, 5).value = rdBuscar("ApellidoMaterno").ToString.Trim
            wb.SHEETS(1).cells(rng, 6).value = rdBuscar("NombreCompleto").ToString.Trim
            fNac = rdBuscar("FechaNacimiento")
            dia = fNac.Day
            mes = fNac.Month
            If dia < 10 Then
                dia = "0" & dia
            End If
            If mes < 10 Then
                mes = "0" & mes
            End If
            anio = Mid(fNac.Year, 3, 2)
            strFnac = anio & mes & dia
            wb.SHEETS(1).cells(rng, 7).value = fNac.Day & "/" & fNac.Month & "/" & fNac.Year
            wb.SHEETS(1).cells(rng, 8).value = rdBuscar("LugarNacimiento").ToString.Trim
            If rdBuscar("EstadoCivil").ToString.Trim = "C" Then
                Civil = "Casado"
            ElseIf rdBuscar("EstadoCivil").ToString.Trim = "S" Then
                Civil = "Soltero"
            ElseIf rdBuscar("EstadoCivil").ToString.Trim = "D" Then
                Civil = "Divorciado"
            ElseIf rdBuscar("EstadoCivil").ToString.Trim = "V" Then
                Civil = "Viudo"

            End If

            wb.SHEETS(1).cells(rng, 9).value = Civil
            If rdBuscar("Sexo").ToString.Trim = "M" Then
                sexo = "Masculino"
            Else
                sexo = "Femenino"
            End If
            wb.SHEETS(1).cells(rng, 10).value = sexo
            wb.SHEETS(1).cells(rng, 11).value = rdBuscar("CURP").ToString.Trim & strFnac & rdBuscar("CURPF").ToString.Trim
            wb.SHEETS(1).cells(rng, 12).value = rdBuscar("Nss").ToString.Trim
            wb.SHEETS(1).cells(rng, 13).value = rdBuscar("RFC").ToString.Trim & strFnac & rdBuscar("Homoclave").ToString.Trim
            wb.SHEETS(1).cells(rng, 14).value = rdBuscar("CuentaPago")
            wb.SHEETS(1).cells(rng, 15).value = rdBuscar("Sd")
            wb.SHEETS(1).cells(rng, 16).value = rdBuscar("Sdi")
            wb.SHEETS(1).cells(rng, 17).value = rdBuscar("depto").ToString.Trim
            wb.SHEETS(1).cells(rng, 18).value = rdBuscar("puesto").ToString.Trim
            wb.SHEETS(1).cells(rng, 19).value = rdBuscar("CuentaCW").ToString.Trim
            wb.SHEETS(1).cells(rng, 20).value = rdBuscar("Direccion").ToString.Trim
            wb.SHEETS(1).cells(rng, 21).value = rdBuscar("CodigoPostal")
            wb.SHEETS(1).cells(rng, 22).value = rdBuscar("Poblacion").ToString.Trim
            wb.SHEETS(1).cells(rng, 23).value = rdBuscar("Estado").ToString.Trim
            wb.SHEETS(1).cells(rng, 24).value = rdBuscar("Telefono").ToString.Trim
            wb.SHEETS(1).cells(rng, 25).value = rdBuscar("celular").ToString.Trim
            wb.SHEETS(1).cells(rng, 26).value = rdBuscar("telefonorecados").ToString.Trim
            wb.SHEETS(1).cells(rng, 27).value = rdBuscar("Escolaridad").ToString.Trim
            If rdBuscar("EstadoEmpleado").ToString.Trim = "A" Then
                estado = "Alta"
            ElseIf rdBuscar("EstadoEmpleado").ToString.Trim = "B" Then
                estado = "Baja"
            Else
                estado = "Reingreso"
            End If
            wb.SHEETS(1).cells(rng, 28).value = estado
            ALTA = rdBuscar("FechaAlta")
            wb.SHEETS(1).cells(rng, 29).value = ALTA.Day & "/" & ALTA.Month & "/" & ALTA.Year
            Try
                re = rdBuscar("FechaReingreso")
                baja = rdBuscar("FechaBaja")
            Catch ex As Exception

            End Try
            If estado <> "Alta" Then
                wb.SHEETS(1).cells(rng, 30).value = re.Day & "/" & re.Month & "/" & re.Year
                wb.SHEETS(1).cells(rng, 31).value = baja.Day & "/" & baja.Month & "/" & baja.Year

            End If

            wb.SHEETS(1).cells(rng, 32).value = rdBuscar("CausaBaja").ToString.Trim

            If rdBuscar("puesto").ToString.Trim = "OPERADOR" Then
                wb.SHEETS(1).cells(rng, 33).value = BuscarStrDatoCitra("Capacitacion", "Tarjeton", "status='ALTA' and idempleado=" & rdBuscar("idempleado"))
                wb.SHEETS(1).cells(rng, 34).value = BuscarStrDatoCitra("Licencias", "Licencia", "idempleado=" & rdBuscar("idempleado"))
                vigencia = BuscarDateDatoCitra("Licencias", "vigencia", "idempleado=" & rdBuscar("idempleado"))
                wb.SHEETS(1).cells(rng, 35).value = vigencia.Day & "/" & vigencia.Month & "/" & vigencia.Year
            Else
                wb.SHEETS(1).cells(rng, 33).value = ""
                wb.SHEETS(1).cells(rng, 34).value = ""
                wb.SHEETS(1).cells(rng, 35).value = ""
            End If
            For i = 1 To 35
                wb.SHEETS(1).cells(rng, i).font.size = 9
            Next
            rng = rng + 1
        Loop

        wb.SHEETS(1).columns(15).numberformat = "#,##0.00"
        wb.SHEETS(1).columns(16).numberformat = "#,##0.00"

        wb.SHEETS(1).COLUMNS(2).ColumnWidth = 8
        wb.SHEETS(1).COLUMNS(3).ColumnWidth = 22
        wb.SHEETS(1).COLUMNS(4).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(5).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(6).ColumnWidth = 40
        wb.SHEETS(1).COLUMNS(7).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(8).ColumnWidth = 20
        wb.SHEETS(1).COLUMNS(9).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(10).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(11).ColumnWidth = 25
        wb.SHEETS(1).COLUMNS(12).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(13).ColumnWidth = 25
        wb.SHEETS(1).COLUMNS(14).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(15).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(16).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(17).ColumnWidth = 32
        wb.SHEETS(1).COLUMNS(18).ColumnWidth = 30
        wb.SHEETS(1).COLUMNS(19).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(20).ColumnWidth = 48
        wb.SHEETS(1).COLUMNS(21).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(22).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(23).ColumnWidth = 20
        wb.SHEETS(1).COLUMNS(24).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(25).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(26).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(27).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(28).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(29).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(30).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(31).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(32).ColumnWidth = 40
        wb.SHEETS(1).COLUMNS(33).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(34).ColumnWidth = 12
        wb.SHEETS(1).COLUMNS(35).ColumnWidth = 12

        wb.SHEETS(1).cells(1, 2).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 2).FONT.SIZE = 20
        wb.SHEETS(1).cells(1, 2).FONT.name = "Segoe UI Light"
        wb.SHEETS(1).cells(2, 2).value = "Informe de Personal Detallado: " & cont & " Registros en el filtro"
        wb.SHEETS(1).cells(2, 2).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 2).FONT.name = "Segoe UI Light"
        wb.SHEETS(1).cells(3, 2).value = "Impreso el " & Now().Day & "-" & Now().Month & "-" & Now().Year & " por " & UsuarioActivo
        wb.SHEETS(1).cells(3, 3).FONT.SIZE = 12
        wb.SHEETS(1).cells(3, 3).FONT.name = "Segoe UI Light"

        wb.SHEETS(1).cells(1, 1).value = ""
        wb.SHEETS(1).columns(1).columnwidth = 3
        wb.sheets(1).cells(1, 1).interior.color = 15773696
        wb.sheets(1).cells(2, 1).interior.color = 15773696


    End Sub

    Private Sub ToolStripButton5_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton5.Click
        EmpleadoNuevo = True
        frmModuloEmpleadosDetalle.ShowDialog()
    End Sub

    Private Sub ImportarDeArchivoToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles ImportarDeArchivoToolStripMenuItem.Click
        frmCargaNombres.ShowDialog()
    End Sub

    Private Sub chActivos_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles chActivos.CheckedChanged
        CargaEmpleados()

    End Sub
    Sub FiltroExcel()
        Dim xl As Object
        Dim wb As Object
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 6

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        wb.SHEETS(1).cells(rng, 2).value = "Codigo"
        wb.SHEETS(1).cells(rng, 3).value = "Nombre"
        wb.SHEETS(1).cells(rng, 4).value = "Departamento"
        wb.SHEETS(1).cells(rng, 5).value = "Puesto"
        wb.SHEETS(1).cells(rng, 6).value = "Status"

        Dim i As Integer
        For i = 2 To 5
            wb.SHEETS(1).cells(rng, i).font.bold = True
            wb.SHEETS(1).cells(rng, i).font.size = 9
        Next

        rng = rng + 1
        Dim cont As Integer = 0

        For m = 0 To dgEmpleados.Rows.Count - 1
            cont = cont + 1
            wb.SHEETS(1).cells(rng, 2).value = dgEmpleados.Rows(m).Cells(1).Value
            wb.SHEETS(1).cells(rng, 3).value = dgEmpleados.Rows(m).Cells(2).Value
            wb.SHEETS(1).cells(rng, 4).value = dgEmpleados.Rows(m).Cells(3).Value
            wb.SHEETS(1).cells(rng, 5).value = dgEmpleados.Rows(m).Cells(4).Value
            wb.SHEETS(1).cells(rng, 6).value = dgEmpleados.Rows(m).Cells(5).Value

            For i = 2 To 6
                wb.SHEETS(1).cells(rng, i).font.size = 9
            Next
            rng = rng + 1
        Next
       

        wb.SHEETS(1).COLUMNS(2).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(3).ColumnWidth = 25
        wb.SHEETS(1).COLUMNS(4).ColumnWidth = 20
        wb.SHEETS(1).COLUMNS(5).ColumnWidth = 15
        wb.SHEETS(1).COLUMNS(6).ColumnWidth = 10

        wb.SHEETS(1).cells(1, 2).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 2).FONT.SIZE = 20
        wb.SHEETS(1).cells(1, 2).FONT.name = "Segoe UI Light"
        wb.SHEETS(1).cells(2, 2).value = "Vista previa del Catálogo de Empleados: " & cont & " Registros en el filtro"
        wb.SHEETS(1).cells(2, 2).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 2).FONT.name = "Segoe UI Light"
        wb.SHEETS(1).cells(3, 2).value = "Impreso el " & Now().Day & "-" & Now().Month & "-" & Now().Year & " por " & UsuarioActivo
        wb.SHEETS(1).cells(3, 3).FONT.SIZE = 12
        wb.SHEETS(1).cells(3, 3).FONT.name = "Segoe UI Light"

        wb.SHEETS(1).cells(1, 1).value = ""
        wb.SHEETS(1).columns(1).columnwidth = 3
        wb.sheets(1).cells(1, 1).interior.color = 15773696
        wb.sheets(1).cells(2, 1).interior.color = 15773696
    End Sub

    Private Sub ToolStripButton2_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton2.Click
        FiltroExcel()

    End Sub

    Private Sub EvaluaciónToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)


    End Sub

    Private Sub DefinirAdeudosTradicionalesToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DefinirAdeudosTradicionalesToolStripMenuItem.Click
        frmAdeudosTradicionales.ShowDialog()

    End Sub

    Private Sub PersonalToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PersonalToolStripMenuItem.Click
        frmRPersonal.ShowDialog()

    End Sub
End Class